package com.ph.irp.stock.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.ph.util.DateUtil;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.model.PStockOrder;
import com.ph.irp.stock.dao.StockOrderDao;
import com.ph.util.DBUtil;

public class StockOrderDaoImpl implements StockOrderDao {
	
	// dataSource is a tool that we use to connect the database
	private ComboPooledDataSource dataSource;
	
	
	//setters and getters for instance variables
	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addStockOrder(PStockOrder stockOrder) throws Throwable{
		// TODO Auto-generated method stub
		//数据库连接
		Connection con = null ;
		//预编译语句
		PreparedStatement ps = null ;
		//结果集
		ResultSet rs = null ;
		
		//构建查询语句
		StringBuilder sql = new StringBuilder();
	
		sql.append("insert into p_stock_stockorder(StockCode,StockWay,BalanceDate,IsCancel,IsClosed,AuditingDate,IsAuditing,Auditinger,BuiltTime,MakeBiller,Remark,SupplierID,CurrencyId,SettlementId,DepartmentID,ManagerID,BusinesserID) values('") ;
		sql.append(stockOrder.getStockCode()) ;
		sql.append("',") ;
		sql.append(stockOrder.getStockWay()) ;
		sql.append(",'") ;
		sql.append(stockOrder.getBalanceDate()) ;
		sql.append("',");
		sql.append(stockOrder.getIsCancel());
		sql.append(",");
		sql.append(stockOrder.getIsClosed()) ;
		sql.append(",") ;
		sql.append("null") ;
		sql.append(",") ;
		sql.append(stockOrder.getIsAuditing());
		sql.append(",");
		sql.append(stockOrder.getAuditinger()) ;
		sql.append(",'") ;
		sql.append(stockOrder.getBuiltTime()) ;
		sql.append("','") ;
		sql.append(stockOrder.getMakeBiller()) ;
		sql.append("','") ;
		sql.append(stockOrder.getRemark());
		sql.append("',") ;
		sql.append(stockOrder.getSupplierId()) ;
		sql.append(",") ;
		sql.append(stockOrder.getCurrencyId()) ;
		sql.append(",") ;
		sql.append(stockOrder.getSettlementId()) ;
		sql.append(",") ;
		sql.append(stockOrder.getDepartmentId()) ;
		sql.append(",") ;
		sql.append(stockOrder.getManagerId()) ;
		sql.append(",") ;
		sql.append(stockOrder.getBusinesserId()) ;
		sql.append(")");
		try {
			con = dataSource.getConnection() ;
			ps = con.prepareStatement(sql.toString()) ;
			ps.execute() ;			
			//ps = con.
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage()) ;
		}finally{
			try {
				DBUtil.setNullObject(con, ps, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw e ;
			}
		}

	}

	@Override
	public void deleteStockOrder(String[] ids)throws Throwable {
		// TODO Auto-generated method stub

	}

	@Override
	public void editStockOrder(PStockOrder stockOrder)throws Throwable {
		// TODO Auto-generated method stub

	}
	@Override
	public int getStockOrderId(String code) throws Throwable {
		// TODO Auto-generated method stub
		
		int stockOrderId = 0 ;
		//数据库连接
		Connection con = null ;
		//预编译语句
		PreparedStatement ps = null ;
		//结果集
		ResultSet rs = null ;
		
		//构建查询语句
		StringBuilder sql = new StringBuilder();
		sql.append("select StockOrderID from p_stock_stockorder where StockCode='") ;
		sql.append(code) ;
		sql.append("'") ;
		try {
			//获得数据库连接
			con = dataSource.getConnection() ;
			//得到预编译语句
			ps = con.prepareStatement(sql.toString()) ;
			//得到结果集
			rs = ps.executeQuery() ;
			rs.next() ;
			
			stockOrderId = rs.getInt(1) ;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage()) ;
		}finally{
			try {
				DBUtil.setNullObject(con, ps, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw e ;
			}
		}

		return stockOrderId;
	}
	@Override
	public List<PStockOrder> getAllStockOrders() throws Throwable{
		// TODO Auto-generated method stub
		//采购订单集合
		List<PStockOrder> stockOrders = new ArrayList<PStockOrder>();
		//数据库连接
		Connection con = null ;
		//预编译语句
		PreparedStatement ps = null ;
		//结果集
		ResultSet rs = null ;
		
		//构建查询语句
		String sql = "select * from p_stock_stockorder" ;
		try {
			con = dataSource.getConnection() ;
			ps = con.prepareStatement(sql) ;
			rs = ps.executeQuery() ; 
			
			while(rs.next()){
				
				//采购订单model
				PStockOrder stockOrder = new PStockOrder();
				stockOrder.setStockOrderId(rs.getInt("StockOrderID"));
				stockOrder.setStockCode(rs.getString("StockCode")) ;
				stockOrder.setStockWay(rs.getInt("StockWay")) ;
				stockOrder.setBalanceDate(DateUtil.date2String(rs.getDate("BalanceDate"), "yyyy-MM-dd")) ;
				stockOrder.setIsCancel(rs.getInt("IsCancel")) ;
				stockOrder.setIsClosed(rs.getInt("IsClosed"));
				stockOrder.setAuditingDate(DateUtil.date2String(rs.getDate("AuditingDate"), "yyyy-MM-dd"));
				stockOrder.setIsAuditing(rs.getInt("IsAuditing")) ;
				stockOrder.setAuditinger(rs.getInt("Auditinger"));
				stockOrder.setBuiltTime(DateUtil.date2String(rs.getDate("BuiltTime"), "yyyy-MM-dd"));
				stockOrder.setMakeBiller(rs.getString("MakeBiller"));
				stockOrder.setRemark(rs.getString("Remark"));
				stockOrder.setSupplierId(rs.getInt("SupplierID"));
				stockOrder.setCurrencyId(rs.getInt("CurrencyId")) ;
				stockOrder.setSettlementId(rs.getInt("SettlementId")) ;
				stockOrder.setDepartmentId(rs.getInt("DepartmentID")) ;
				stockOrder.setManagerId(rs.getInt("ManagerID"));
				stockOrder.setBusinesserId(rs.getInt("BusinesserID")) ;
				
				stockOrders.add(stockOrder);
			}
			//ps = con.
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage()) ;
		}finally{
			try {
				DBUtil.setNullObject(con, ps, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw e ;
			}
		}	
		return stockOrders;
	}
	/**
	 * 
	 */
	@Override
	public List<PStockOrder> getStockOrdersByCondition(
			PStockOrder stockOrderModel, String startDate, String endDate)
			throws Throwable {
		// TODO Auto-generated method stub
		//采购订单集合
		List<PStockOrder> stockOrders = new ArrayList<PStockOrder>();
		//数据库连接
		Connection con = null ;
		//预编译语句
		PreparedStatement ps = null ;
		//结果集
		ResultSet rs = null ;
		
		//构建查询语句
		StringBuilder sql = new StringBuilder();
		sql.append("select * from p_stock_stockorder where 1=1") ;
		//采购订单代码
		if(!stockOrderModel.getStockCode().trim().equals("")){
			sql.append(" and StockCode='");
			sql.append(stockOrderModel.getStockCode().trim());		
			sql.append("'") ;
		}
		//采购订单审核状态
		if(stockOrderModel.getIsAuditing()==0||stockOrderModel.getIsAuditing()==1){
			sql.append(" and IsAuditing=");
			sql.append(stockOrderModel.getIsAuditing());
		}
		//采购订单日期
		if(!startDate.equals("")&&!endDate.equals("")){
			sql.append(" and BuiltTime between '") ;
			sql.append(startDate) ;
			sql.append("' and '") ;
			sql.append(endDate) ;
			sql.append("'") ;
		}
		//采购订单供应商
		if(stockOrderModel.getSupplierId()!=0){
			sql.append(" and SupplierID=") ;
			sql.append(stockOrderModel.getSupplierId());
		}
		try {
			con = dataSource.getConnection() ;
			ps = con.prepareStatement(sql.toString()) ;
			rs = ps.executeQuery() ; 
			System.out.println(sql.toString());
			while(rs.next()){
				
				//采购订单model
				PStockOrder stockOrder = new PStockOrder();
				stockOrder.setStockOrderId(rs.getInt("StockOrderID"));
				stockOrder.setStockCode(rs.getString("StockCode")) ;
				stockOrder.setStockWay(rs.getInt("StockWay")) ;
				stockOrder.setBalanceDate(DateUtil.date2String(rs.getDate("BalanceDate"), "yyyy-MM-dd")) ;
				stockOrder.setIsCancel(rs.getInt("IsCancel")) ;
				stockOrder.setIsClosed(rs.getInt("IsClosed"));
				stockOrder.setAuditingDate(DateUtil.date2String(rs.getDate("AuditingDate"), "yyyy-MM-dd"));
				stockOrder.setIsAuditing(rs.getInt("IsAuditing")) ;
				stockOrder.setAuditinger(rs.getInt("Auditinger"));
				stockOrder.setBuiltTime(DateUtil.date2String(rs.getDate("BuiltTime"), "yyyy-MM-dd"));
				stockOrder.setMakeBiller(rs.getString("MakeBiller"));
				stockOrder.setRemark(rs.getString("Remark"));
				stockOrder.setSupplierId(rs.getInt("SupplierID"));
				stockOrder.setCurrencyId(rs.getInt("CurrencyId")) ;
				stockOrder.setSettlementId(rs.getInt("SettlementId")) ;
				stockOrder.setDepartmentId(rs.getInt("DepartmentID")) ;
				stockOrder.setManagerId(rs.getInt("ManagerID"));
				stockOrder.setBusinesserId(rs.getInt("BusinesserID")) ;
				
				stockOrders.add(stockOrder);
			}
			//ps = con.
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage()) ;
		}finally{
			try {
				DBUtil.setNullObject(con, ps, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw e ;
			}
		}	
		return stockOrders;
	}
	@Override
	public PStockOrder getStockOrderById(int stockOrderId) throws Throwable {
		// TODO Auto-generated method stub
		
		//采购订单model
		PStockOrder stockOrder = new PStockOrder();
		//数据库连接
		Connection con = null ;
		//预编译语句
		PreparedStatement ps = null ;
		//结果集
		ResultSet rs = null ;
		
		//构建查询语句
		String sql = "select * from p_stock_stockorder where StockOrderID=?" ;
		try {
			//得到数据源
			con = dataSource.getConnection() ;
			ps = con.prepareStatement(sql) ;
			ps.setInt(1, stockOrderId) ;
			rs = ps.executeQuery() ; 
			//封装采购订单model
			if(rs.next()){
				
				stockOrder.setStockOrderId(rs.getInt("StockOrderID"));
				stockOrder.setStockCode(rs.getString("StockCode")) ;
				stockOrder.setStockWay(rs.getInt("StockWay")) ;
				stockOrder.setBalanceDate(DateUtil.date2String(rs.getDate("BalanceDate"), "yyyy-MM-dd")) ;
				stockOrder.setIsCancel(rs.getInt("IsCancel")) ;
				stockOrder.setIsClosed(rs.getInt("IsClosed"));
				stockOrder.setAuditingDate(DateUtil.date2String(rs.getDate("AuditingDate"), "yyyy-MM-dd"));
				stockOrder.setIsAuditing(rs.getInt("IsAuditing")) ;
				stockOrder.setAuditinger(rs.getInt("Auditinger"));
				stockOrder.setBuiltTime(DateUtil.date2String(rs.getDate("BuiltTime"), "yyyy-MM-dd"));
				stockOrder.setMakeBiller(rs.getString("MakeBiller"));
				stockOrder.setRemark(rs.getString("Remark"));
				stockOrder.setSupplierId(rs.getInt("SupplierID"));
				stockOrder.setCurrencyId(rs.getInt("CurrencyId")) ;
				stockOrder.setSettlementId(rs.getInt("SettlementId")) ;
				stockOrder.setDepartmentId(rs.getInt("DepartmentID")) ;
				stockOrder.setManagerId(rs.getInt("ManagerID"));
				stockOrder.setBusinesserId(rs.getInt("BusinesserID")) ;
				
			}
			//ps = con.
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage()) ;
		}finally{
			try {
				DBUtil.setNullObject(con, ps, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw e ;
			}
		}	
		return stockOrder;
	}

}
